<?php
/**
 * Created by PhpStorm.
 * User: Максим
 * Date: 30.07.13
 * Time: 9:57
 */

function insert_marka($marka)
{
    global $db_asiaparts;

    $result = $db_asiaparts->query("SELECT `marka_id` FROM `asiapart`.`sh_marka` WHERE marka = '$marka'");
    if ($result->num_rows > 0)
    {
        return $result->fetch_object()->marka_id;
    }
    else
    {
        $db_asiaparts->query("INSERT INTO `asiapart`.`sh_marka` (`marka`) VALUES ('$marka')");
        return $db_asiaparts->insert_id;
    }
}

function insert_model($model)
{
    global $db_asiaparts;

    $model = $db_asiaparts->real_escape_string($model);
    $result = $db_asiaparts->query("SELECT `model_id` FROM `asiapart`.`sh_model` where `model` = '$model'");
    if ($result->num_rows > 0)
    {
        return $result->fetch_object()->model_id;
    }
    else
    {
        $db_asiaparts->query("INSERT INTO `asiapart`.`sh_model` (`model`) VALUES ('$model')");
        return $db_asiaparts->insert_id;
    }
}

function insert_car($marka_id, $model_id, $year_from, $year_to)
{
    global $db_asiaparts;

    $result = $db_asiaparts->query("
        SELECT `car_id` FROM `asiapart`.`sh_car`
        WHERE `marka_id` = '$marka_id' AND `model_id` = '$model_id' AND
        `year_start` = '$year_from' AND `year_end` = '$year_to'");
    if ($result->num_rows > 0)
    {
        return $result->fetch_object()->car_id;
    }
    else
    {
        $db_asiaparts->query("
            INSERT INTO `asiapart`.`sh_car` (`marka_id`, `model_id`, `year_start`, `year_end`)
            VALUES ('$marka_id', '$model_id', '$year_from', '$year_to')");
        return $db_asiaparts->insert_id;
    }
}

$db_avtopro = new mysqli('avtopro.msgroup.ua', 'u_avtopro2', 'H1AuXxH6', 'avtopro');
$db_asiaparts = new mysqli('localhost', 'root', 'root', 'asiapart');

$avtopro_result = $db_avtopro->query(
        "SELECT
      `ex_marka`.`marka`,
      `ex_model`.`model`,
        SUBSTRING_INDEX(
            SUBSTRING_INDEX(`ex_model`.`years`, '-', 1), '.', -1
        ) year_from,

        SUBSTRING_INDEX(
            SUBSTRING_INDEX(`ex_model`.`years`, '-', -1), '.', -1
        ) year_to,
      `ex_oe_codes`.`oe`
    FROM
      `avtopro`.`ex_marka`
      INNER JOIN `avtopro`.`ex_model`
        ON (
          `ex_marka`.`id` = `ex_model`.`marka_id`
        )
      INNER JOIN `avtopro`.`ex_oe_codes`
        ON (
          `ex_model`.`oe_id` = `ex_oe_codes`.`id`
        )
    ORDER BY marka ");

while ($avtopro_row = $avtopro_result->fetch_object()):

    $oe = preg_replace("/[^0-9a-zA-Z]+/", '', $avtopro_row->oe);
    $asiaparts_product_result = $db_asiaparts->query(
        "SELECT `product_id`, `manufacturer`
        FROM `asiapart`.`sh_product`
        WHERE article = '{$oe}'");

    while ($appr = $asiaparts_product_result->fetch_object()):

        $marka_id = insert_marka($avtopro_row->marka);   print $marka_id . ' ';
        $model_id = insert_model($avtopro_row->model);   print $model_id . " ";

        $year_from = trim($avtopro_row->year_from);
        $year_to   = trim($avtopro_row->year_to);

        $car_id = insert_car($marka_id, $model_id, $year_from, $year_to); //  print $car_id . "\n";

        $db_asiaparts->query("INSERT INTO `asiapart`.`sh_product_car` (`product_id`, `car_id`) VALUES ('{$appr->product_id}', '$car_id')");
        print $appr->product_id . ' - ' . $car_id ."\n";

    endwhile;

endwhile;